package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.OfficialNews;
import com.wanmait.hotelManage.vo.Pager;

public class OfficialNewsDAO {
	private OfficialNewsDAO() {};
	private static OfficialNewsDAO officialnewsdao=new OfficialNewsDAO();
	public static OfficialNewsDAO getOfficialnewsdao() {
		return officialnewsdao;
	}
	//获得行数
	public int getRows() {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select count(*) c from official_news where enable=1";
		
		try {
			conn = DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			return rs.getInt("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
	//获得所有的官方通知
	public List<OfficialNews> findAllOfficialNews(Pager pager) {
		// TODO Auto-generated method stub
		List<OfficialNews> officialNewss=new ArrayList<OfficialNews>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM official_news WHERE ENABLE=1 order by input_time desc limit ?,?";
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				OfficialNews officialNews=new OfficialNews();
				officialNews.setId(rs.getInt("id"));
				officialNews.setContent(rs.getString("content"));
				officialNews.setInputTime(rs.getTimestamp("input_time"));
				officialNews.setTitle(rs.getString("title"));
				
				officialNewss.add(officialNews);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return officialNewss;
	}
	//根据id删除
	public void delete(int id) {
		// TODO Auto-generated method stub
		String sql="update official_news set enable=0 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql,id);
	}
	//批量删除
	public void delete(String ids) {
		// TODO Auto-generated method stub
		String sql="update official_news set enable=0 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	//增加通知
	public void insert(OfficialNews officialNews) {
		// TODO Auto-generated method stub
		String sql="insert into official_news(title,content) values(?,?)";
		DBHelper.getDBHelper().executeUpdate(sql,officialNews.getTitle(),officialNews.getContent());
	}
	//根据关键词搜索
	public List<OfficialNews> findOfficialNewssBykeyword(String keyword, Pager pager) {
		// TODO Auto-generated method stub
		List<OfficialNews> officialNewss=new ArrayList<OfficialNews>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM official_news " + 
				"WHERE ENABLE=1 " + 
				"AND (content like '%"+keyword+"%' " + 
				"or  title like '%"+keyword+"%') "+
				"limit ?,? ";
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
		
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				OfficialNews officialNews=new OfficialNews();
				officialNews.setContent(rs.getString("content"));
				officialNews.setId(rs.getInt("id"));
				officialNews.setInputTime(rs.getTimestamp("input_time"));
				officialNews.setTitle(rs.getString("title"));
				officialNewss.add(officialNews);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}

		return officialNewss;
	}
	//获得搜索后的行数
	public int getRows(String keyword) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select count(*) c from official_news where enable=1 and (title "
				+ "like '%"+keyword+"%' or content like '%"+keyword+"%')";
		
		try {
			conn = DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			return rs.getInt("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
	
}
